﻿using System;
using System.Collections.Generic;
using GCTrackableRace.Interfaces;
using System.Data;
using System.Data.SQLite;
using GcTrackableRace.Entities;
using log4net;

namespace GcTrackableRace.Persistence
{
	public class GsakPersistence: BaseSqlitePersistence, IGsakPersistence
	{
		private readonly IProgramConfigurationSettings _ProgramConfigurationSettings;
		private readonly ILog _Log;

		public GsakPersistence(IProgramConfigurationSettings programConfigurationSettings, ILog log)
		{
			_ProgramConfigurationSettings = programConfigurationSettings;
			_Log = log;
		}

		public IEnumerable<ITrackable> GetAllTrackables()
		{
			_Log.Info(String.Format("GetAllTrackables: Enter"));

			const string sqlStatement =
				"Select Code, Name, PlacedBy, Country, State, County, UserData, User3, Symbol from Caches order by Name ASC";

			var trackables = new List<ITrackable>();

			using (var conn = new SQLiteConnection(GetConnectionString(_ProgramConfigurationSettings.TrackablesDatabaseNameAndLocation)))
			{
				var cmd = new SQLiteCommand(sqlStatement, conn);

				SQLiteDataReader reader = null;

				conn.Open();

				try
				{
					_Log.Debug(String.Format("GetAllTrackables: Reading from Trackables Table."));

					reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);

					_Log.Debug(String.Format("GetAllTrackables: Processing reader."));

					while (reader.Read())
					{
						trackables.Add(
						               new Trackable
						               	{
						               		Code = reader["Code"].ToString(),
						               		Name = reader["Name"].ToString(),
						               		Owner = reader["PlacedBy"].ToString(),
						               		Country = reader["Country"].ToString(),
						               		State = reader["State"].ToString(),
						               		County = reader["County"].ToString(),
						               		TrackableType = reader["UserData"].ToString(),
						               		TrackableParentName = reader["User3"].ToString()
						               	}
							);
					}
				}
				finally
				{
					if (reader != null)
						reader.Close();

					_Log.Debug(String.Format("GetAllTrackables: Closed reader and connection."));
				}
			}

			return trackables;
		}

		public IEnumerable<ITrackableLog> GetAllTrackableLogs(string trackableCode)
		{
			_Log.Info(String.Format("GetAllTrackableLogs: Enter"));

			const string sqlStatement =
				"Select lLogId, lBy, lType, lDate, lLat, lLon, lParent as TrackableCode from Logs where lParent=@trackableCode order by lDate ASC, lLogId ASC;";

			var trackableLogs = new List<ITrackableLog>();

			using (var conn = new SQLiteConnection(GetConnectionString(_ProgramConfigurationSettings.TrackablesDatabaseNameAndLocation)))
			{
				var cmd = new SQLiteCommand(sqlStatement, conn);

				cmd.Parameters.Add(new SQLiteParameter("@trackableCode", trackableCode));

				_Log.Debug(String.Format("GetAllTrackableLogs: Calling GetDataFromPersistence()"));

				GetDataFromPersistence(trackableLogs, conn, cmd);
			}

			return trackableLogs;
		}

		public IEnumerable<ITrackableLog> GetTrackableLogs(string trackableCode, DateTime startDate, DateTime endDate)
		{
			_Log.Info(String.Format("GetTrackableLogs: Enter"));

			const string sqlStatement =
				"Select lLogId, lBy, lType, lDate, lLat, lLon, lParent as TrackableCode from Logs " +
				"where lParent=@trackableCode and lDate >= @startDate and lDate <= @endDate " + 
				"order by lDate ASC, lLogId ASC;";

			var trackableLogs = new List<ITrackableLog>();

			using (var conn = new SQLiteConnection(GetConnectionString(_ProgramConfigurationSettings.TrackablesDatabaseNameAndLocation)))
			{
				var cmd = new SQLiteCommand(sqlStatement, conn);

				cmd.Parameters.Add(new SQLiteParameter("@trackableCode", trackableCode));
				cmd.Parameters.Add(new SQLiteParameter("@startDate", startDate));
				cmd.Parameters.Add(new SQLiteParameter("@endDate", endDate));

				_Log.Debug(String.Format("GetTrackableLogs: Calling GetDataFromPersistence()"));
				
				GetDataFromPersistence(trackableLogs, conn, cmd);
			}

			return trackableLogs;
		}

		public IGeocache GetCacheDetails(string gcCode)
		{
			const string sqlStatement = "Select Code, Name, CacheType, Country, State, County, Elevation, Longitude, Latitude, Container, Difficulty, Terrain from Caches where Code=@gcCode;";

			Geocache geocache = null;

			using (var conn = new SQLiteConnection(GetConnectionString(_ProgramConfigurationSettings.GeocacheDatabaseNameAndLocation)))
			{
				var cmd = new SQLiteCommand(sqlStatement, conn);

				cmd.Parameters.Add(new SQLiteParameter("@gcCode", gcCode));

				SQLiteDataReader reader = null;

				conn.Open();

				try
				{
					reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);

					while (reader.Read())
					{
						//var cachetype = reader["CacheType"].ToString();

						geocache = new Geocache
						{
							Code = reader["Code"].ToString(),
							Name = reader["Name"].ToString(),
							Type = reader["CacheType"].ToString(),
								//cachetype == "R" ? GeocacheType.EarthCache :
								//cachetype == "E" ? GeocacheType.Event :
								//cachetype == "T" ? GeocacheType.TraditionalCache :
								//GeocacheType.Unknown,
							Country = reader["Country"].ToString(),
							State = reader["State"].ToString(),
							County = reader["County"].ToString(),
							Elevation = Double.Parse(reader["Elevation"].ToString()),
							Latitude = Double.Parse(reader["Latitude"].ToString()),
							Longitude = Double.Parse(reader["Longitude"].ToString()),
							Container = reader["Container"].ToString(),
							Difficulty = Decimal.Parse(reader["Difficulty"].ToString()),
							Terrain = Decimal.Parse(reader["Terrain"].ToString())
						};
					}
				}
				finally
				{
					if (reader != null)
						reader.Close();
				}
			}

			return geocache;
		}

		private void GetDataFromPersistence(List<ITrackableLog> trackableLogs, SQLiteConnection conn, SQLiteCommand cmd)
		{
			_Log.Info(String.Format("GetDataFromPersistence: Enter"));

			SQLiteDataReader reader = null;

			_Log.Debug(String.Format("GetDataFromPersistence: Opening Sql connection"));

			conn.Open();

			try
			{
				
				reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);

				_Log.Debug(String.Format("GetDataFromPersistence: Calling PopulateItemLogFromReader()"));

				PopulateItemLogFromReader(trackableLogs, reader);
			}
			finally
			{
				if (reader != null)
					reader.Close();

				_Log.Debug(String.Format("GetDataFromPersistence: Closed reader and connection."));
			}
		}

		private void PopulateItemLogFromReader(List<ITrackableLog> logs, SQLiteDataReader reader)
		{
			_Log.Info(String.Format("PopulateItemLogFromReader: Enter"));

			while (reader.Read())
			{
				var lBy = reader["lBy"].ToString().Split(new[] {"$~"}, 2, StringSplitOptions.None);

				if (lBy.Length == 1)
				{
					Array.Resize(ref lBy, 2);
					lBy[1] = lBy[0];
					lBy[0] = "<no cache>";
				}

				logs.Add(
				    new TrackableLog
				    {
				        LogId = int.Parse(reader["lLogId"].ToString()),
				        TrackableCode = reader["TrackableCode"].ToString(),
				        LogType = reader["lType"].ToString(),
				        LocationGcCode = lBy[0],
				        ByCacher = lBy[1],
				        Date = DateTime.Parse(reader["lDate"].ToString()),
				        Latitude = String.IsNullOrEmpty(reader["lLat"].ToString()) ? 0 : Double.Parse(reader["lLat"].ToString()),
				        Longitude = String.IsNullOrEmpty(reader["lLon"].ToString()) ? 0 : Double.Parse(reader["lLon"].ToString()),
				    }
				);
			}
		}
	}
}